﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using REPORT.DTO;
namespace REPORT.DAL
{
   public class DAL_CHITIETHOADON
    {
       public int ThemCTHD(DTO_CHITIETHOADON obj)
       {
           DbProvider db = new DbProvider();
           int result = 0;
           string strInsert = @"INSERT INTO chitiethoadon (ma_hoa_don,ma_san_pham, so_luong, don_gia, don_vi_tinh) 
                                VALUES(@mahd,@masp,@sl, @dg, @dvt)";
           db.AddParameter(new SqlParameter("@mahd", obj.MaHoaDon));
           db.AddParameter(new SqlParameter("@masp", obj.MaSanPham));
           db.AddParameter(new SqlParameter("@sl", obj.SoLuong));
           db.AddParameter(new SqlParameter("@dg", obj.DonGia));
           db.AddParameter(new SqlParameter("@dvt", obj.DonViTinh));
           try
           {
               result = db.ExcuteNoneQuery(strInsert);
           }
           catch (Exception ex) { throw new Exception(ex.Message); }
           return result;
       }
       public DataTable MaHD(int mahd)
       {
           DataTable dt = new DataTable();
           DbProvider db = new DbProvider();
           string sql = @"select ma_hoa_don, chitiethoadon.ma_san_pham, ten_san_pham, chitiethoadon.so_luong, chitiethoadon.don_gia, 
                            ChiTietHoaDon.don_vi_tinh,
                            chitiethoadon.so_luong*chitiethoadon.don_gia as thanh_tien  
                            from chitiethoadon left join SanPham on 
                            ChiTietHoaDon.ma_san_pham = SanPham.ma_san_pham where ma_hoa_don=@mahd";
           db.AddParameter(new SqlParameter("@mahd", mahd));
           try
           {
               dt = db.ExecuteQuery(sql);
           }
           catch (Exception ex) { throw new Exception(ex.Message); }
           return dt;
       }
       public int UpdateSoLuong(DTO_CHITIETHOADON obj)
       {
           DbProvider db = new DbProvider();
           int result = 0;
           string strInsert = @"UPDATE chitiethoadon SET so_luong=@sl WHERE ma_hoa_don=@mahd and ma_san_pham=@masp";
           db.AddParameter(new SqlParameter("@sl", obj.SoLuong));
           db.AddParameter(new SqlParameter("@mahd", obj.MaHoaDon));
           db.AddParameter(new SqlParameter("@masp", obj.MaSanPham));
           try
           {
           result = db.ExcuteNoneQuery(strInsert);
           }
           catch (Exception ex) { throw new Exception(ex.Message); }
           return result;
       }
       public int XoaHangCTHD(DTO_CHITIETHOADON obj)
       {
           DbProvider db = new DbProvider();
           int result = 0;
           string strDelete = @"DELETE chitiethoadon WHERE ma_hoa_don = @mahd and ma_san_pham=@masp";
           db.AddParameter(new SqlParameter("@mahd", obj.MaHoaDon));
           db.AddParameter(new SqlParameter("@masp", obj.MaSanPham));
           try
           {
           result = db.ExcuteNoneQuery(strDelete);
           }
           catch (Exception ex) { throw new Exception(ex.Message); }
           return result;
       }
       public DataTable LoadTheoMaHD(int mahd)
       {
           DataTable dt = new DataTable();
           DbProvider db = new DbProvider();
           string sql = @"select ma_hoa_don, chitiethoadon.ma_san_pham, ten_san_pham, chitiethoadon.so_luong, chitiethoadon.don_gia, 
                            ChiTietHoaDon.don_vi_tinh,
                            chitiethoadon.so_luong*chitiethoadon.don_gia as thanh_tien  
                            from chitiethoadon left join SanPham on 
                            ChiTietHoaDon.ma_san_pham = SanPham.ma_san_pham where ma_hoa_don=@mahd";
           db.AddParameter(new SqlParameter("@mahd", mahd));
           try
           {
               dt = db.ExecuteQuery(sql);
           }
           catch (Exception ex) { throw new Exception(ex.Message); }
           return dt;
       }

       public int ThemCTHDCu(DTO_CHITIETHOADON obj)
       {
           DbProvider db = new DbProvider();
           int result = 0;
           string strInsert = @"INSERT INTO chitiethoadon (ma_hoa_don,ma_san_pham, so_luong, don_gia, don_vi_tinh) 
                                VALUES(@mahd,@masp,@sl, @dg, @dvt)";
           db.AddParameter(new SqlParameter("@mahd", obj.MaHoaDon));
           db.AddParameter(new SqlParameter("@masp", obj.MaSanPham));
           db.AddParameter(new SqlParameter("@sl", obj.SoLuong));
           db.AddParameter(new SqlParameter("@dg", obj.DonGia));
           db.AddParameter(new SqlParameter("@dvt", obj.DonViTinh));
           try
           {
               result = db.ExcuteNoneQuery(strInsert);
           }
           catch (Exception ex) { throw new Exception(ex.Message); }
           return result;
       }
       public bool KiemTraTonTaiMaHD(int maHD)
       {
           DbProvider db = new DbProvider();
           DataTable dt = new DataTable();
           string strTest = @"SELECT ma_hoa_don FROM chitiethoadon WHERE ma_hoa_don = @mahd";
           db.AddParameter(new SqlParameter("@mahd", maHD));
           try
           {
               dt = db.ExecuteQuery(strTest);
           }
           catch (Exception ex)
           {
               throw new Exception(ex.Message);
           }
           if (dt.Rows.Count > 0)
           {
               return false;
           }
           return true;
       }
     }
}
